﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class BookInfoDAL
    {
        SqlCommand cmd;//定义SqlCommand对象
        public BookInfoDAL()
        {
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;//使用存储过程访问数据库
        }
        public DataSet GetBookClass()
        {
            cmd.CommandText = "GetClass";//指定存储过程名称
            cmd.Parameters.Clear();//清除存储过程参数值

            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        /// <summary>
        /// 数据库查重
        /// </summary>
        public bool CheckingDulication(BookInfo bo)
        {
            cmd.CommandText = "GetBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 15).Value = bo.BookID;
            object obj = DBHelper.GetScalar(cmd);
            if (obj != null)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 图书修改方法实现
        /// </summary>
        /// <param name="bo">BookInfo对象</param>
        /// <param name="operation">操作标记</param>
        /// <returns>布尔值</returns>
        public bool EditBook(BookInfo bo,string operation)
        {
            if (operation == "I")
                cmd.CommandText = "InsertNewBook"; //当操作值为"I"，进行新增操作
            if (operation == "U")
                cmd.CommandText = "UpdateBook"; //当操作值为"U"，进行更新操作
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 15).Value = bo.BookID;
            cmd.Parameters.Add("@BookName", SqlDbType.VarChar, 200).Value = bo.BookName;
            cmd.Parameters.Add("@ISBN", SqlDbType.Char, 20).Value = bo.ISBN;
            cmd.Parameters.Add("@Author", SqlDbType.VarChar, 50).Value = bo.Author;
            cmd.Parameters.Add("@Publisher", SqlDbType.VarChar, 40).Value = bo.Publisher;
            cmd.Parameters.Add("@BookVersion", SqlDbType.VarChar, 40).Value = bo.BookVersion;

            cmd.Parameters.Add("@WordCount", SqlDbType.Int).Value = bo.WordCount;
            cmd.Parameters.Add("@PageCount", SqlDbType.Int).Value = bo.PageCount;

            cmd.Parameters.Add("@PublishDate", SqlDbType.DateTime).Value = bo.PublishDate;

            cmd.Parameters.Add("@ClassID", SqlDbType.Char,10).Value = bo.BookClass.BookClassID;

            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                if (num > 0)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 读取图书类别数据源
        /// </summary>
        /// <param name="reader">数据库读取对象</param>
        /// <returns>图书类别对象：BookClass</returns>
        private static BookClass GetBookCalssByDataReader(SqlDataReader reader)
        {
            string bookClassID = reader["classId"].ToString().Trim();
            string bookClassName = reader["className"].ToString().Trim();
            BookClass obj = new BookClass(bookClassID, bookClassName);
            return obj;
        }
        /// <summary>
        /// 获取图书信息
        /// </summary>
        /// <param name="bookClassID">图书类别编号值</param>
        /// <param name="bookClassName">图书类别名称</param>
        /// <returns>图书类别对象：BookClass</returns>
        public BookClass FindBookClass(string bookClassID = null, string bookClassName = null)
        {
            BookClass obj = new BookClass();
            if (bookClassID == null)
                obj.BookClassID = "";
            else obj.BookClassID = bookClassID;
            if (bookClassName == null)
                obj.BookClassName = "";
            else obj.BookClassName = bookClassName;
            cmd.CommandText = "GetBookClassByBookClass";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookClassID", SqlDbType.VarChar, 10).Value = obj.BookClassID;
            cmd.Parameters.Add("@bookClassName", SqlDbType.VarChar, 50).Value = obj.BookClassName;
            SqlDataReader reader = DBHelper.ExcuteReader(cmd);
            while (reader.Read())
            {
                BookClass o = GetBookCalssByDataReader(reader);
                reader.Close();
                return o;
            }
            reader.Close();
            return null;
        }
        /// <summary>
        /// 获取图书信息
        /// </summary>
        /// <param name="bookname">图书名称</param>
        /// <param name="bookClassName">图书类别名称</param>
        /// <returns>图书类别对象：BookClass</returns>
        public DataSet GetBookByBookNameOrClassname(string bookname,string classname)
        {
            string classid = "";
            BookClass bo = new BookClass();
            bo.BookClassName = classname;
            if (bo.BookClassName.Length > 0)
            {
                classid = FindBookClass(bo.BookClassID, bo.BookClassName).BookClassID;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBookInfoBySearch";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookName", SqlDbType.NVarChar, 200).Value = bookname;
            cmd.Parameters.Add("@bookClassid", SqlDbType.VarChar, 10).Value = classid;

            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        /// <summary>
        /// 根据索书号获取相关记录
        /// </summary>
        /// <param name="bookid">索书号</param>
        /// <returns>数据集</returns>
        public DataSet GetBookByBookID(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 15).Value = bookid;
            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        public bool DeleteBook(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "DeleteBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 15).Value = bookid;

            var returnVal = cmd.Parameters.Add("@returnValue", SqlDbType.Int);//定义存储过程输出参数output
            returnVal.Direction = ParameterDirection.Output;                   //说明output为存储过程输出参数
            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                int val = Convert.ToInt16(returnVal.Value);
                if (num > 0 || val==1)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据读者编号查询图书借阅信息
        /// </summary>
        /// <param name="userid">读者编号</param>
        /// <returns>借阅数据集</returns>
        public DataSet GetBorrowBookByUserID(string userid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBorrowInfoByUserID";//定义存储过程名字
            cmd.Parameters.Clear();//清空参数
            cmd.Parameters.Add("@userid", SqlDbType.VarChar, 11).Value = userid;//添加存储过程参数信息
            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        /// <summary>
        /// 新增借书信息
        /// </summary>
        /// <param name="bookid">索书号</param>
        /// <param name="userid">读者证号</param>
        /// <returns>对象</returns>
        public object InsertBorrowInfo(string bookid,string userid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "InsertBorrowInfo";//定义存储过程名字
            cmd.Parameters.Clear();//清空参数
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 20).Value = bookid;//添加存储过程参数信息
            cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 11).Value = userid;//添加存储过程参数信息

            var returnVal = cmd.Parameters.Add("@resultVal", SqlDbType.Int);//定义存储过程输出参数output
            returnVal.Direction = ParameterDirection.Output;                   //说明output为存储过程输出参数
            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                int val = Convert.ToInt16(returnVal.Value);
                if (num > 0 || val == 1)
                    return true;
                else
                    return false;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据索书号获取图书状态
        /// false：图书已借出，不可再借
        /// true：图书在架，可借
        /// </summary>
        /// <param name="bookid"></param>
        /// <returns></returns>
        public bool BookSateByBookID(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBookStateByBookID";//定义存储过程名字
            cmd.Parameters.Clear();//清空参数
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 20).Value = bookid;//添加存储过程参数信息

            var returnVal = cmd.Parameters.Add("@returnValue", SqlDbType.Int);//定义存储过程输出参数output
            returnVal.Direction = ParameterDirection.Output;                   //说明output为存储过程输出参数

            try
            {
                object state = DBHelper.ExcuteReader(cmd).ToString();//读数据库
                var output = returnVal.Value.ToString();
                return Convert.ToBoolean(output);
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 还书管理，图书/读者/借阅信息读取
        /// </summary>
        /// <param name="userid">读者编号</param>
        /// <param name="bookid">索书号</param>
        /// <returns>DataSet数据集</returns>
        public DataSet GetBorrowInfo(string userid, string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBorrowInfo";//定义存储过程名字
            cmd.Parameters.Clear();//清空参数
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 20).Value = bookid;//添加存储过程参数信息
            cmd.Parameters.Add("@userid", SqlDbType.VarChar, 11).Value = userid;//添加存储过程参数信息

            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        /// <summary>
        /// 还书处理
        /// </summary>
        /// <param name="bookid"></param>
        /// <returns></returns>
        public bool ReturnBook(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "ReturnBook";//定义存储过程名字
            cmd.Parameters.Clear();//清空参数
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 15).Value = bookid;//添加存储过程参数信息

            int num= DBHelper.ExecuteSQL(cmd);

            try
            {
                if (num > 0)
                    return true;
                else
                    return false;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
    }
}
